package cn.chiship.sdk.core.util;

import cn.chiship.sdk.core.enums.DatabaseTypeEnum;
import cn.chiship.sdk.core.exception.custom.BusinessException;
import cn.chiship.sdk.core.exception.custom.SystemErrorException;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * JDBC工具类
 *
 * @author lijian
 */
public class JdbcUtil {

	private static final Logger LOGGER = LoggerFactory.getLogger(JdbcUtil.class);

	private static final String MYSQL_PREFIX = "com.mysql";

	private static final String SQLSERVER_PREFIX = "com.microsoft.sqlserver";

	private static final String ORACLE_PREFIX = "oracle.jdbc";

	/**
	 * 驱动
	 */
	private final String driver;

	/**
	 * 链接路径
	 */
	private final String url;

	/**
	 * 定义数据库的链接
	 */
	private final Connection conn;

	/**
	 * 定义sql语句的执行对象
	 */
	private PreparedStatement pstmt;

	/**
	 * 定义查询返回的结果集合
	 */
	private ResultSet rs;

	private final DatabaseTypeEnum databaseType;

	/**
	 * 初始化
	 * @param driver 驱动
	 * @param url 链接
	 * @param username 用户名
	 * @param password 密码
	 */
	public JdbcUtil(String driver, String url, String username, String password) {

		try {

			this.driver = driver;
			this.url = url;
			databaseType = analysisDatabaseType();
			Class.forName(driver);
			conn = DriverManager.getConnection(url, username, password);
			LOGGER.info("数据库连接成功");
		}
		catch (Exception e) {
			throw new SystemErrorException(e);
		}

	}

	/**
	 * 更新数据
	 * @param sql sql语句
	 * @param params 参数集合
	 * @return 结果
	 * @throws SQLException 异常信息
	 */
	public boolean updateByParams(String sql, List<Object> params) throws SQLException {

		// 影响行数
		pstmt = conn.prepareStatement(sql);
		int index = 1;
		// 填充sql语句中的占位符
		if (null != params && !params.isEmpty()) {
			for (Object param : params) {
				pstmt.setObject(index++, param);
			}
		}

		return pstmt.executeUpdate() > 0;

	}

	/**
	 * 查询多条记录
	 * @param sql sql语句
	 * @param params 参数
	 * @return 结果
	 * @throws SQLException 异常信息
	 */
	public List<Map<String, Object>> selectByParams(String sql, List<Object> params) throws SQLException {

		List<Map<String, Object>> list = new ArrayList<>();
		int index = 1;
		pstmt = conn.prepareStatement(sql);
		if (null != params && !params.isEmpty()) {
			for (Object param : params) {
				pstmt.setObject(index++, param);
			}
		}

		rs = pstmt.executeQuery();
		ResultSetMetaData metaData = rs.getMetaData();
		int colsLen = metaData.getColumnCount();
		while (rs.next()) {
			Map<String, Object> map = new HashMap<>(colsLen);
			for (int i = 0; i < colsLen; i++) {
				String columnName = metaData.getColumnName(i + 1);
				Object columnValue = rs.getObject(columnName);
				if (null == columnValue) {
					columnValue = "";
				}
				map.put(columnName.toLowerCase(), columnValue);
			}
			list.add(map);
		}
		return list;

	}

	/**
	 * 根据数据库获得所有表名
	 * @param database 数据库
	 * @return 结果
	 * @throws SQLException 异常信息
	 */
	public List<Map<String, Object>> selectTablesByDatabase(String database) throws SQLException {
		return selectTablesByDatabase(database, null);
	}

	public List<Map<String, Object>> selectTablesByDatabase(String database, String tableName) throws SQLException {
		if (StringUtil.isNullOrEmpty(database)) {
			throw new BusinessException("参数【database】不能为空!");
		}
		String sql = null;
		switch (databaseType) {
		case DATABASE_TYPE_MYSQL:
			sql = "SELECT table_name,table_comment FROM INFORMATION_SCHEMA.TABLES WHERE UPPER(table_schema) = " + "'"
					+ StringUtil.upperCase(database) + "'";
			if (!StringUtil.isNullOrEmpty(tableName)) {
				sql += " AND UPPER(table_name) = '" + StringUtil.upperCase(tableName) + "'";
			}
			break;
		case DATABASE_TYPE_SQL_SERVER:
			sql = "select  a.name AS table_name, CONVERT(NVARCHAR(100),isnull(g.[value],a.name)) AS table_comment from sys.tables a left join sys.extended_properties g on (a.object_id = g.major_id AND g.minor_id = 0) ";
			if (!StringUtil.isNullOrEmpty(tableName)) {
				sql += " where  UPPER(a.name) = '" + StringUtil.upperCase(tableName) + "'";
			}
			break;
		case DATABASE_TYPE_ORACLE:

			sql = "select allt.table_name,(select comments from all_tab_comments cms where cms.table_name=allt.table_name) as table_comment from all_tables  allt where UPPER(allt.owner)='"
					+ StringUtil.upperCase(database) + "' ";

			if (!StringUtil.isNullOrEmpty(tableName)) {
				sql += " and UPPER(allt.table_name) ='" + StringUtil.upperCase(tableName) + "'";
			}
			break;
		default:
			throw new BusinessException("暂不支持的数据库类型");
		}
		LOGGER.info("发送SQL:{}", sql);
		List<Map<String, Object>> result = selectByParams(sql, null);
		for (Map<String, Object> map : result) {
			String name = map.get("table_name").toString();
			String comment = map.get("table_comment").toString();
			if (StringUtil.isNullOrEmpty(comment)) {
				comment = name;
			}
			if ("-".equals(comment)) {
				comment = name;
			}
			map.put("name", name);
			map.put("comment", comment);
			map.remove("table_comment");
			map.remove("table_name");
		}
		return result;
	}

	/**
	 * 根据数据库及表名获得表列属性
	 * @param database 数据库
	 * @param tableName 表名
	 * @return 结果
	 * @throws SQLException 异常
	 */
	public List<Map<String, Object>> selectColumnsByTable(String database, String tableName) throws SQLException {
		if (StringUtil.isNullOrEmpty(database)) {
			throw new BusinessException("参数【database】不能为空!");
		}
		if (StringUtil.isNullOrEmpty(tableName)) {
			throw new BusinessException("参数【tableName】不能为空!");
		}
		String sql = null;
		switch (databaseType) {
		case DATABASE_TYPE_MYSQL:
			sql = "SELECT column_name,column_type,column_key,is_nullable,column_comment FROM INFORMATION_SCHEMA"
					+ ".columns WHERE  UPPER(table_schema) = '" + StringUtil.upperCase(database)
					+ "' AND UPPER(table_name) = '" + StringUtil.upperCase(tableName) + "'";
			break;
		case DATABASE_TYPE_SQL_SERVER:
			sql = "SELECT\n" + "a.name N'column_name',\n" + "b.name N'column_type',\n"
					+ "(case when (select count(*) from sysobjects \n"
					+ "where (name in  (select name from sysindexes \n"
					+ "where (id = a.id) AND (indid in  (select indid  from sysindexkeys  \n"
					+ "where (id = a.id) AND (colid in (select colid  from syscolumns \n"
					+ "where (id = a.id) AND (name = a.name))))))) AND  (xtype = 'PK'))>0 then 'PK' else '' end) N'column_key', \n"
					+ "(case when a.isnullable=1 then 'true'else 'false' end) N'is_nullable', \n"
					+ "CONVERT(NVARCHAR(100),isnull(g.[value],'')) AS N'column_comment' \n" + "from syscolumns a \n"
					+ "left join systypes b \n" + "on a.xtype=b.xusertype \n" + "inner join sysobjects d \n"
					+ "on a.id=d.id and d.xtype='U' and d.name<>'dtproperties' \n" + "left join syscomments e \n"
					+ "on a.cdefault=e.id \n" + "left join sys.extended_properties g \n"
					+ "on a.id=g.major_id AND a.colid = g.minor_id \n" + "where UPPER(d.name)='"
					+ StringUtil.upperCase(tableName) + "' order by a.colorder\n" + "\n";
			break;
		case DATABASE_TYPE_ORACLE:
			sql = " SELECT USER_TAB_COLS.TABLE_NAME   as column_name, "
					+ "        USER_TAB_COLS.DATA_TYPE    as column_type, "
					+ "        USER_TAB_COLS.NULLABLE     as is_nullable, "
					+ "        user_col_comments.comments as column_comment, "
					+ "       case  (select cons.constraint_type from user_constraints cons where cons.table_name=USER_TAB_COLS.TABLE_NAME) when 'P' then 'PK' else  "
					+ "         (select cons.constraint_type from user_constraints cons where cons.table_name=USER_TAB_COLS.TABLE_NAME) end as column_key "
					+ "   FROM USER_TAB_COLS  " + "  inner join user_col_comments "
					+ "     on user_col_comments.TABLE_NAME = USER_TAB_COLS.TABLE_NAME "
					+ "    and user_col_comments.COLUMN_NAME = USER_TAB_COLS.COLUMN_NAME "
					+ " and USER_TAB_COLS.TABLE_NAME='" + StringUtil.upperCase(tableName) + "' ";
			break;
		default:
			throw new BusinessException("暂不支持的数据库类型");
		}
		LOGGER.info("发送SQL:{}", sql);

		return selectByParams(sql, null);
	}

	/**
	 * 分析数据库类型
	 * @return 结果
	 */
	private DatabaseTypeEnum analysisDatabaseType() {
		DatabaseTypeEnum databaseTypeEnum = null;
		if (this.getDriver().contains(MYSQL_PREFIX)) {
			databaseTypeEnum = DatabaseTypeEnum.DATABASE_TYPE_MYSQL;
		}
		if (this.getDriver().contains(SQLSERVER_PREFIX)) {
			databaseTypeEnum = DatabaseTypeEnum.DATABASE_TYPE_SQL_SERVER;
		}
		if (this.getDriver().contains(ORACLE_PREFIX)) {
			databaseTypeEnum = DatabaseTypeEnum.DATABASE_TYPE_ORACLE;
		}
		if (StringUtil.isNull(databaseTypeEnum)) {
			throw new BusinessException("无法识别的数据库类型");
		}
		return databaseTypeEnum;
	}

	public static void main(String[] args) throws SQLException {
		/*
		 * JdbcUtil jdbcUtil = new JdbcUtil( "com.mysql.cj.jdbc.Driver",
		 * "jdbc:mysql://localhost:3306/chiship_common_upms?useUnicode=true&characterEncoding=UTF8&useSSL=false&serverTimezone=Asia/Shanghai",
		 * "root", "123456"); log.info(JSON.toJSONString(jdbcUtil.selectTablesByDatabase(
		 * "chiship_common_upms")));
		 * log.info(JSON.toJSONString(jdbcUtil.selectColumnsByTable("chiship_common_upms",
		 * "upms_data_dict")));
		 */

		/*
		 * JdbcUtil jdbcUtil = new JdbcUtil(
		 * "com.microsoft.sqlserver.jdbc.SQLServerDriver",
		 * "jdbc:sqlserver://47.104.247.47;Databasename=DLL_Workflow", "dll", "zy@123");
		 * log.info(JSON.toJSONString(jdbcUtil.selectColumnsByTable("DLL_Workflow",
		 * "workflow_form_value")));
		 */

		/*
		 * JdbcUtil jdbcUtil = new JdbcUtil( "oracle.jdbc.driver.OracleDriver",
		 * "jdbc:oracle:thin:@10.1.3.29:1521:oradb", "pcisv6_tsky", "pcisv6_tsky_11");
		 *
		 * log.info(JSON.toJSONString(jdbcUtil.selectTablesByDatabase("pcisv6_tsky",
		 * "DIP_INPATIENT_MX")));
		 * log.info(JSON.toJSONString(jdbcUtil.selectColumnsByTable("pcisv6_tsky",
		 * "DIP_INPATIENT_MX")));
		 */

	}

	/**
	 * 释放连接
	 */
	public void release() {

		try {
			if (null != rs) {
				rs.close();
			}

			if (null != pstmt) {
				pstmt.close();
			}

			if (null != conn) {
				conn.close();
			}
		}
		catch (SQLException e) {
			LOGGER.error("发生异常", e);
		}
		LOGGER.info("释放数据库连接");

	}

	public String getDriver() {
		return driver;
	}

	public String getUrl() {
		return url;
	}

}
